import os
import traceback
import uuid

import pandas
from pypinyin import Style, pinyin
from utilsapp import utils
import hashlib  # 加密相关
import json
import time
from datetime import datetime, timedelta
import jwt
import requests
from flask import Blueprint, request, current_app
from jwt import exceptions
from utilsapp.mysql_con import (
    db_connect,
    db_insert_id,
    db_list,
    db_pagelist,
    db_insert,
    db_delete,
    db_table_list,
    db_operation,
    db_fetchone,
    db_update,
)
from utilsapp.utils import aes_decrypt

user = Blueprint("user", __name__)


# 获取指定用户信息
@user.route("/getinfo")
def get_user_info():
    user_id = int(request.args.get("id"))
    sql = f"SELECT id,user_name,nick_name,login_token,token_time,role_id FROM userinfo where id = {user_id}"
    data = db_operation(sql)
    return data[0]


# 登录
@user.route("/auth", methods=["POST"])
def auth_login():
    user_name = request.json.get("user_name")
    pwd_input = request.json.get("password")
    pwd_input = aes_decrypt(pwd_input)
    token_input = request.json["access_token"] if "access_token" in request.json else ""
    source = request.json["source"] if "source" in request.json else ""  # 来源
    is_token_login = token_input and len(token_input) > 0
    if is_token_login:
        pwd_input = "1"
        if not user_name:
            return {"code": -1, "msg": "自动登录失败"}

    err_cs = get_err_count(user_name)
    if err_cs and int(err_cs) >= 6:
        return {"code": -1, "msg": "登录失败,错误次数过多,十分钟后重试"}

    sql = f"select id,role_id,password from userinfo where user_name='{user_name}'"
    sql += f" AND password = '{token_input}'" if len(token_input) > 0 else ""
    res = db_connect(sql)

    if res["code"] == -1:  # 数据库连接失败
        return res
    else:  # 连接成功验证密码
        if not res["data"]:
            err_count(user_name)
            return {"code": -1, "msg": "登录失败"}
        else:
            password = res["data"][0]["password"]
            role_id = res["data"][0]["role_id"]
            id = res["data"][0]["id"]
            hl = hashlib.md5()  # 加密
            hl.update(pwd_input.encode(encoding="utf-8"))
            if hl.hexdigest() == password or is_token_login:  # 密码验证成功
                login_token = create_token(
                    user_name, role_id, current_app.config["settings"]["SECRET_KEY"]
                )
                # 更新用户的login_token
                sql1 = (
                    f'UPDATE userinfo SET login_token = "{login_token}"where id= "{id}"'
                )
                res = db_operation(sql1)  # 更新用户登录状态，token
                if res["Affected_rows"] >= 1:
                    login_count(user_name)
                    return {"code": 0, "msg": "登录成功", "login_token": login_token}
                else:
                    err_count(user_name)
                    return {"code": -1, "msg": "登录失败"}
            else:  # 密码验证失败
                if err_cs == 0:
                    reset_login(user_name)

                err_count(user_name)
                return {
                    "code": -1,
                    "msg": f"登录失败{err_cs + 1}次,失败6次后将会被限制登录",
                }



# 登录
@user.route("/login", methods=["POST"])
def user_login():
    user_name = request.json.get("user_name")
    pwd_input = request.json.get("password")
    pwd_input = aes_decrypt(pwd_input)
    token_input = request.json["access_token"] if "access_token" in request.json else ""
    source = request.json["source"] if "source" in request.json else ""  # 来源
    is_token_login = token_input and len(token_input) > 0
    if is_token_login:
        pwd_input = "1"
        if not user_name:
            return {"code": -1, "msg": "自动登录失败"}

    err_cs = get_err_count(user_name)
    if err_cs and int(err_cs) >= 6:
        return {"code": -1, "msg": "登录失败,错误次数过多,十分钟后重试"}

    sql = f"select id,role_id,password from userinfo where user_name='{user_name}'"
    sql += f'and password={pwd_input}' if len(pwd_input)>0 and is_token_login==False else ''

    res = db_connect(sql)

    if res["code"] == -1:  # 数据库连接失败
        return res

    if not res["data"]:
        err_count(user_name)
        return {"code": -1, "msg": "登录失败"}
    
    password = res["data"][0]["password"]
    role_id = res["data"][0]["role_id"]
    id = res["data"][0]["id"]
    hl = hashlib.md5()  # 加密
    hl.update(pwd_input.encode(encoding="utf-8"))
    if hl.hexdigest() == password or is_token_login:  # 密码验证成功
        login_token = create_token(
            user_name, role_id, current_app.config["settings"]["SECRET_KEY"]
        )
        # 更新用户的login_token
        sql1 = (
            f'UPDATE userinfo SET login_token = "{login_token}"where id= "{id}"'
        )
        res = db_operation(sql1)  # 更新用户登录状态，token
        if res["Affected_rows"] >= 1:
            login_count(user_name)
            return {"code": 0, "msg": "登录成功", "login_token": login_token}
        else:
            err_count(user_name)
            return {"code": -1, "msg": "登录失败"}
    else:  # 密码验证失败
        if err_cs == 0:
            reset_login(user_name)

        err_count(user_name)
        return {
            "code": -1,
            "msg": f"登录失败{err_cs + 1}次,失败6次后将会被限制登录",
        }


def err_count(username):
    sql = f"UPDATE userinfo set err_count=IFNULL(err_count,0)+1,err_time = NOW() where user_name = '{username}'"
    return db_operation(sql)


def login_count(username):
    sql = f"UPDATE userinfo set login_count=IFNULL(login_count,0)+1,err_count=0 where user_name = '{username}'"
    db_operation(sql)


def reset_login(username):
    sql = f"UPDATE userinfo set err_count=0 where user_name = '{username}'"
    db_operation(sql)


def get_err_count(username):
    sql = f"SELECT err_count FROM userinfo WHERE err_time >= DATE_SUB(NOW(), INTERVAL 10 MINUTE) and user_name = '{username}'"
    e = db_fetchone(sql)["data"]
    return e if e else 0


# 注册
@user.route("/add", methods=["POST"])
def user_add():
    user_name = request.json.get("user_name")
    password = request.json.get("password")
    nick_name = request.json.get("nick_name")
    role_id = request.json.get("role_id")
    notify = request.json.get("notify")
    speech = request.json.get("speech")
    mail = request.json.get("mail")
    message = request.json.get("message")
    phonenumber = request.json.get("phonenumber")
    role_id = request.json.get("role_id")
    sql_1 = f""" SELECT id from userinfo WHERE user_name = '{user_name}'"""
    res_1 = db_connect(sql_1)
    if res_1["data"] is None or not res_1["data"]:
        hl = hashlib.md5()  # 加密
        hl.update(password.encode(encoding="utf-8"))
        sql = f"""INSERT into userinfo (user_name,`password`,nick_name,role_id,notify,speech,mail,message,phonenumber)
        VALUES ('{user_name}','{hl.hexdigest()}','{nick_name}',{role_id},{notify},{speech},{mail},{message},{'NULL' if phonenumber == None else phonenumber})"""

        res = db_operation(sql)

        if res["Affected_rows"] >= 1:
            return {"msg": "注册成功", "code": 0}
        else:
            return {"msg": "注册失败", "code": -1}
    else:
        return {"msg": "该用户已存在", "code": -1}


@user.route("/delete", methods=["post"])
def user_delete():
    id = request.json["id"]
    return db_delete("userInfo", f"WHERE id=%s", {"id": id})


# 获取当前用户的信息
@user.route("/getself")
def user_get_self():
    headers = request.headers
    token = headers["Authorization"]

    info = jwt.decode(
        token, current_app.config["settings"]["SECRET_KEY"], algorithms="HS256"
    )

    user_name = info["user_name"]
    sql = f"""select kk.*,r.type from (select a.phonenumber,a.id as user_id,a.user_name,b.permissionIdList,a.nick_name,a.notify,a.speech,a.mail,a.message,b.dataId,b.operate,CONCAT('/static',b.logo) as logo ,b.`menu`,b.`desc` as role_name,b.id as role_id from userinfo a,
        role b where a.user_name='{user_name}' and a.role_id=b.id) kk left join regionlevel r on kk.dataId= r.id"""
    res = db_connect(sql)
    return res["data"][0]


@user.route("/edit", methods=["post"])
def user_edit():
    id = request.json["id"]
    keys_not_add = ["id"]
    addModel = {
        key: request.json[key] for key in request.json if key not in keys_not_add
    }
    return db_update("userinfo", addModel, f"WHERE id = %s", {"id": id})


@user.route("/reset", methods=["post"])
def user_reset():
    id = request.json["id"]
    keys_not_add = ["id"]
    addModel = {
        key: request.json[key] for key in request.json if key not in keys_not_add
    }
    hl = hashlib.md5()  # 加密
    hl.update("123456".encode(encoding="utf-8"))
    addModel["password"] = hl.hexdigest()
    return db_update("userinfo", addModel, f"WHERE id = %s", {"id": id})


@user.route("/getmenu", methods=["get"])
def menu_list():
    sql = "select * from menu order by id"
    return db_connect(sql)


@user.route("/password", methods=["post"])
def user_password():
    id = request.json["id"]
    password = request.json["password"]
    password = aes_decrypt(password)
    keys_not_add = ["id"]
    addModel = {
        key: request.json[key] for key in request.json if key not in keys_not_add
    }
    hl = hashlib.md5()  # 加密
    hl.update(password.encode(encoding="utf-8"))
    addModel["password"] = hl.hexdigest()
    return db_update("userinfo", addModel, f"WHERE id = %s", {"id": id})


@user.route("/getlist", methods=["POST"])
def get_list():
    a = request.json
    nick_name = assign_value(a, "nick_name")
    scope_id = assign_value(a, "scope_id")
    # 基础语句
    sql = """SELECT u.* from userinfo u
		LEFT JOIN role r on u.role_id = r.id"""
    # 条件语句
    query = " where 1=1 "
    if nick_name != "":
        query += f" and u.nick_name like '%{nick_name}%'"
    if scope_id != "":
        query += f" and r.dataId = {scope_id}"
    sql = sql + query + " ORDER BY u.id desc"
    res = db_pagelist(sql, request.json["page"], request.json["size"])
    return res


# 生成token
def create_token(user_name, role_id, key):
    payload = {
        "exp": datetime.now()
        + timedelta(hours=current_app.config["settings"]["over_time"]),
        "user_name": user_name,
        "role_id": role_id,
    }

    token = jwt.encode(payload=payload, key=key, algorithm="HS256")

    return token


# 验证token
def verify_token(token, key, source):
    rs = None
    hl = hashlib.md5()  # 加密
    hl.update(f"{token}{key}".encode(encoding="utf-8"))
    rkey = hl.hexdigest()

    try:
        rvalue = current_app.redis.get(rkey)
        if rvalue is not None:
            return json.loads(rvalue)

        # 解析token拿到用户名和角色id
        info = jwt.decode(token, key, algorithms="HS256")
        exp = info["exp"]
        role_id = info["role_id"]
        now = int(time.time())
        user_name = info["user_name"]
        sql = f'SELECT login_token,app_token,id FROM userinfo where user_name= "{user_name}"'
        sql_1 = f"""SELECT r.dataId ,r.desc,r.permissionIdList,r.warnpermission  FROM role r,regionlevel l 
        where  r.dataId=l.id AND r.id= {role_id}"""
        id = db_connect(sql)["data"][0]["id"]

        if len(str(id)) <= 0:
            return {"msg": "token认证成功", "code": -1000, "flag": {"dataId": 0}}
        res = db_connect(sql_1)
        flag = {"dataId": 0} if not res["data"] else res["data"][0]

        rs = {"msg": "token认证成功", "code": 0, "flag": flag}
    except Exception as e:
        rs = {"msg": f"token已失效{str(e)}", "code": -1000, "flag": {"dataId": 0}}

    # except exceptions.ExpiredSignatureError as e:  # 'token已失效'

    #     rs =  {"msg": f"token已失效{str(e)}", "code": -1000, "flag": {"dataId": 0}}

    # except jwt.DecodeError as e():  # 'token认证失败'

    #     rs =  {"msg": "token认证失败", "code": -1000, "flag": {"dataId": 0}}

    # except jwt.InvalidTokenError:  # '非法的token'

    #     rs =  {"msg": "非法的token", "code": -1000, "flag": {"dataId": 0}}
    # except Exception:
    #     rs =  {"msg": "token认证失败", "code": -1000, "flag": {"dataId": 0}}

    current_app.redis.set(rkey, json.dumps(rs), ex=60 * 10)

    return rs


# 北海登陆继承
def sync_users():
    try:
        o = {
            "code": "CIP-USER",
            "data": {
                "startTime": "2022-01-01 00:00:00",
                "endTime": "2099-01-07 23:00:00",
            },
        }
        data = json.dumps(o)
        res = requests.post(
            "http://10.13.126.34:8091/sinoma-interface/api/common", data=data, timeout=5
        )
        if res.status_code != 200:
            print("同步用户请求失败", res.status_code)
            return
        j = res.json()
        if not j["success"]:
            print("同步用户数据失败", res.text)
            return

        print("获取用户数据成功")
        dbres = db_list("SELECT user_name FROM userinfo")
        old_ids = [] if dbres["code"] != 0 else [t["user_name"] for t in dbres["data"]]
        add_models = [to_user_db(n) for n in j["data"] if n["code"] not in old_ids]
        if len(add_models) <= 0:
            print("同步用户数据成功", "暂无新数据同步")
            return
        dbres = db_insert("userinfo", add_models)
        if dbres["code"] == 0:
            print("同步用户数据成功", "影响行数", dbres["Affected_rows"])
            return

        print("同步用户数据失败", dbres)
    except Exception as e:
        print("同步用户数据异常", e)


def login_bytoken(token):
    try:
        o = {
            "code": "signOn",
            "header": {
                "Sinoma-Auth": "bearer " + token,
            },
            "data": {},
        }
        data = json.dumps(o)
        res = requests.post(
            "http://10.13.126.34:8091/sinoma-interface/api/common", data=data, timeout=5
        )
        if res.status_code != 200 or len(res.text) <= 0:
            print("login_token 失败", res.text, res.status_code)
            return ""
        return res.json()["account"]
    except Exception as e:
        print("login_token 异常", e)
        return ""


def to_user_db(o):
    return {
        "user_name": o["code"],
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "nick_name": o["realName"],
        "role_id": 28,
        "user_roles": "data",
        "user_id": o["code"],
    }


@user.route("/upload", methods=["POST"])
def upload():
    try:
        # 检查请求中是否包含文件
        if "file" not in request.files:
            return utils.fail("空文件")
        file = request.files["file"]
        # 检查文件是否为空
        if file.filename == "":
            return utils.fail("空文件")
        script_dir = os.path.dirname(os.path.abspath(__name__)) + "/static"
        date = time.strftime("%Y%m%d", time.localtime())
        ext = os.path.splitext(file.filename)[-1]

        if "xls" not in ext:
            return utils.fail(None, "导入失败,文件格式不正确")

        fileName = f"/upload/{date}"
        filePath = script_dir + fileName
        if not os.path.exists(filePath):
            os.mkdir(filePath)

        fileName = f"/upload/{date}/{uuid.uuid1()}{ext}"
        filePath = script_dir + fileName

        # 保存文件到指定目录
        file.save(filePath)
        df = pandas.read_excel(filePath)
        row_count = len(df)
        if row_count <= 0:
            return utils.fail("excel 暂无数据")
        department = db_list("SELECT * FROM department")["data"]
        role = db_list("SELECT * FROM role")["data"]
        for i in range(row_count):
            row = df.loc[i]
            data = {
                "id": 1000 + i,
                "user_name": "".join(
                    [p[0] for p in pinyin(row["姓名"], style=Style.NORMAL)]
                ),
                "password": "e10adc3949ba59abbe56e057f20f883e",
                "nick_name": row["姓名"],
                "department_id": [
                    obj
                    for obj in department
                    if obj.get("name") == (row["部门"].split("/")[1])
                ][0]["id"],
                "role_id": (
                    [obj for obj in role if row["角色"] in obj.get("name")][0]["id"]
                    if str(row["角色"]) != "nan"
                    else 1000
                ),
                "notify": 4,
                "speech": 4,
                "mail": 4,
                "message": 4,
                "phonenumber": row["手机"][4:],
            }
            db_insert_id("userinfo", data)
        return utils.ok("")
    except Exception as e:
        error_message = traceback.format_exc()
        current_app.add_log("测点导入", error_message)
        return utils.fail(None, "导入失败", e)


def assign_value(dictionary, key, default_=""):
    try:
        if dictionary[key] is None:
            return default_
        else:
            return dictionary[key]
    except KeyError:
        return default_
